package com.sharepics.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.sharepics.bean.Album;
import com.sharepics.bean.Picture;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;


public class AlbumDAO {

	public static final String LOGTAG = AlbumDAO.class.getName();
    public static final String DB_NAME = "sharepics_db";
    public static final String DB_TABLE = "spics_album";
    public static final int DB_VERSION = 5;

    private static final String CLASSNAME = AlbumDAO.class.getSimpleName();
    private static final String[] COLS = new String[] { "_id", "name","sent","remoteid"};
    
    public static final String CLASSNAME_PIC = PictureDAO.class.getSimpleName();
    public static final String[] COLS_PIC = new String[] { "_id","albumid","name", "latitude", "longitude", "date","sent","uri","content_type"};


    private SQLiteDatabase db;
    private final DBOpenHelper dbOpenHelper;
    private PictureDAO pictureDAO;

   
    private static class DBOpenHelper extends SQLiteOpenHelper {

        private static final String DB_CREATE = "CREATE TABLE "
            + AlbumDAO.DB_TABLE
            + " (_id INTEGER PRIMARY KEY,  name TEXT UNIQUE NOT NULL, sent INTEGER NOT NULL DEFAULT 0,remoteid INTEGER);";

        public DBOpenHelper(final Context context) {
            super(context, AlbumDAO.DB_NAME, null, AlbumDAO.DB_VERSION);
        }

        @Override
        public void onCreate(final SQLiteDatabase db) {
            try {
                db.execSQL(DBOpenHelper.DB_CREATE);
                db.execSQL(PictureDAO.DB_CREATE);
            } catch (SQLException e) {
                Log.e(LOGTAG, AlbumDAO.CLASSNAME, e);
            }
        }

        @Override
        public void onOpen(final SQLiteDatabase db) {
            super.onOpen(db);
        }

        @Override
        public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + AlbumDAO.DB_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + PictureDAO.DB_TABLE);
            onCreate(db);
        }
    }

    //
    // end inner classes
    //

    public AlbumDAO(final Context context) {
        this.dbOpenHelper = new DBOpenHelper(context);
        establishDb();
        pictureDAO = new PictureDAO();
    }

    private void establishDb() {
        if (this.db == null) {
            this.db = this.dbOpenHelper.getWritableDatabase();
        }
    }

    public void cleanup() {
        if (this.db != null) {
            this.db.close();
            this.db = null;
        }
    }

    public void insert(final Album album) {
        ContentValues values = new ContentValues();
        values.put("name", album.name);
        values.put("sent", album.sent);
        values.put("remoteid", album.remoteId);
        this.db.insert(AlbumDAO.DB_TABLE, null, values);
    }
    

    
    public void insertPicture(Picture picture,Album album){
    	pictureDAO.insert(picture, album);
    }

    public void deletePicture(Picture picture){
    	pictureDAO.delete(picture.id);
    }
    
    public void update(final Album album) {
        ContentValues values = new ContentValues();
        values.put("name", album.name);
        values.put("sent", album.sent);
        values.put("remoteid", album.remoteId);
        this.db.update(AlbumDAO.DB_TABLE, values, "_id=" + album.id, null);
    }

    public void delete(final long id) {
        this.db.delete(AlbumDAO.DB_TABLE, "_id=" + id, null);
    }
    
    

    public Album get(final long id) {
        Cursor c = null;
        Album album = null;
        try {
            c = this.db.query(true, AlbumDAO.DB_TABLE, AlbumDAO.COLS, "_id = '" + id + "'", null, null, null, null,
                null);
            if (c.getCount() > 0) {
                c.moveToFirst();
                album = new Album();
                album.id = c.getLong(0);
                album.name = c.getString(1);
                album.sent = c.getInt(2) == 1;
                album.remoteId = c.getLong(3);
            }
        } catch (SQLException e) {
            Log.v(LOGTAG, AlbumDAO.CLASSNAME, e);
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return album;
    }
    
    

    public List<Album> getAll() {
        ArrayList<Album> ret = new ArrayList<Album>();
        Cursor c = null;
        try {
            c = this.db.query(AlbumDAO.DB_TABLE, AlbumDAO.COLS, null, null, null, null, null);
            int numRows = c.getCount();
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {
                Album album = new Album();
                album.id = c.getLong(0);
                album.name = c.getString(1);
                album.sent = c.getInt(2) == 1;
                album.remoteId = c.getLong(3);
                ret.add(album);
                c.moveToNext();
            }
        } catch (SQLException e) {
            Log.v(LOGTAG, AlbumDAO.CLASSNAME, e);
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return ret;
    }
    
    public List<Album> getAllNotSent() {
        ArrayList<Album> ret = new ArrayList<Album>();
        Cursor c = null;
        try {
            c = this.db.query(AlbumDAO.DB_TABLE, AlbumDAO.COLS, "sent = 0", null, null, null, null);
            int numRows = c.getCount();
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {
                Album album = new Album();
                album.id = c.getLong(0);
                album.name = c.getString(1);
                album.sent = c.getInt(2) == 1;
                album.remoteId = c.getLong(3);
                ret.add(album);
                c.moveToNext();
            }
        } catch (SQLException e) {
            Log.v(LOGTAG, AlbumDAO.CLASSNAME, e);
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return ret;
    }
    
    public List<Picture> getAllPicturesNotSent() {
       return pictureDAO.getAllNotSent();
    }

    private class PictureDAO {
    	
        public static final String DB_TABLE = "spics_picture";

        public static final String DB_CREATE = "CREATE TABLE "
                + PictureDAO.DB_TABLE
                + " (_id INTEGER PRIMARY KEY,albumid INTEGER, name TEXT,latitude REAL,longitude REAL,date INTEGER NOT NULL,sent INTEGER NOT NULL DEFAULT 0,uri TEXT NOT NULL,content_type TEXT NOT NULL);";



        public void insert(final Picture picture,Album album) {
            ContentValues values = new ContentValues();
            values.put("name", picture.name);
            values.put("albumid", album.getId());
            values.put("latitude", picture.latitude);
            values.put("longitude", picture.longitude);
            values.put("longitude", picture.longitude);
            Date date = new Date();
            values.put("date", date.getTime());
            values.put("sent", picture.sent);
            values.put("uri", picture.uri);
            values.put("content_type", picture.contentType);
            db.insert(PictureDAO.DB_TABLE, null, values);
        }


        public void delete(final long id) {
            db.delete(PictureDAO.DB_TABLE, "_id=" + id, null);
        }

        public Picture get(final long id) {
            Cursor c = null;
            Picture picture = null;
            try {
                c = db.query(true, PictureDAO.DB_TABLE, COLS_PIC, "_id = '" + id + "'", null, null, null, null,
                    null);
                if (c.getCount() > 0) {
                    c.moveToFirst();
                    picture = new Picture();
                    picture.id = c.getLong(0);
                    picture.albumId = c.getLong(1);
                    picture.name = c.getString(2);
                    picture.latitude = c.getDouble(3);
                    picture.longitude = c.getDouble(4);
                    picture.date = new Date(c.getLong(5));
                    picture.sent = c.getInt(6) == 1;
                    picture.uri = c.getString(7);
                    picture.contentType = c.getString(8);
                }
            } catch (SQLException e) {
                Log.v(LOGTAG, CLASSNAME_PIC, e);
            } finally {
                if (c != null && !c.isClosed()) {
                    c.close();
                }
            }
            return picture;
        }

        public List<Picture> getAll() {
            ArrayList<Picture> ret = new ArrayList<Picture>();
            Cursor c = null;
            try {
                c = db.query(PictureDAO.DB_TABLE, COLS_PIC, null, null, null, null, null);
                int numRows = c.getCount();
                c.moveToFirst();
                for (int i = 0; i < numRows; ++i) {
                	Picture picture = new Picture();
                    picture.id = c.getLong(0);
                    picture.albumId = c.getLong(1);
                    picture.name = c.getString(2);
                    picture.latitude = c.getDouble(3);
                    picture.longitude = c.getDouble(4);
                    picture.date = new Date(c.getLong(5));
                    picture.sent = c.getInt(6) == 1;
                    picture.uri = c.getString(7);
                    picture.contentType = c.getString(8);
                    ret.add(picture);
                    c.moveToNext();
                }
            } catch (SQLException e) {
                Log.v(LOGTAG, CLASSNAME_PIC, e);
            } finally {
                if (c != null && !c.isClosed()) {
                    c.close();
                }
            }
            return ret;
        }
    
    
    public List<Picture> getAllNotSent() {
        ArrayList<Picture> ret = new ArrayList<Picture>();
        Cursor c = null;
        try {
            c = db.query(PictureDAO.DB_TABLE, COLS_PIC, "sent = 0", null, null, null, null);
            int numRows = c.getCount();
            c.moveToFirst();
            for (int i = 0; i < numRows; ++i) {
            	Picture picture = new Picture();
                picture.id = c.getLong(0);
                picture.albumId = c.getLong(1);
                picture.name = c.getString(2);
                picture.latitude = c.getDouble(3);
                picture.longitude = c.getDouble(4);
                picture.date = new Date(c.getLong(5));
                picture.sent = c.getInt(6) == 1;
                picture.uri = c.getString(7);
                picture.contentType = c.getString(8);
                ret.add(picture);
                c.moveToNext();
            }
        } catch (SQLException e) {
            Log.v(LOGTAG, CLASSNAME_PIC, e);
        } finally {
            if (c != null && !c.isClosed()) {
                c.close();
            }
        }
        return ret;
    }

    }
}
